﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;

namespace DAL
{
    public class Articles
    {
        public bool GetArticle(string id, out Util.Article art)
        {
            SqlDataReader sdr;
            art = new Util.Article();

            using (SqlConnection con = new SqlConnection())
            {
                con.ConnectionString = ConfigurationManager.ConnectionStrings["FProj"].ConnectionString;
                con.Open();
                String strSQL = "SELECT * FROM Articles WHERE ArticleID = @ID";
                SqlCommand comm = new SqlCommand(strSQL, con);

                SqlParameter param = new SqlParameter();
                param.ParameterName = "@ID";
                param.Value = id;
                param.SqlDbType = SqlDbType.NVarChar;
                comm.Parameters.Add(param);

                sdr = comm.ExecuteReader(CommandBehavior.CloseConnection);

                if (sdr != null)
                {
                    while (sdr.Read())
                    {
                        art = new Util.Article { ArticleID = sdr["ArticleID"].ToString(), Title = sdr["Title"].ToString(), ArticleBody = sdr["ArticleBody"].ToString(), UserName = sdr["UserName"].ToString(), Date = sdr["Date"].ToString() };
                    }
                }
            }

            return true;
        }

        public bool GetArticlesList(out List<Util.Article> artList)
        {
            SqlDataReader sdr;

            artList = new List<Util.Article>();

            using (SqlConnection con = new SqlConnection())
            {
                con.ConnectionString = ConfigurationManager.ConnectionStrings["FProj"].ConnectionString;
                con.Open();
                String strSQL = "SELECT ArticleID, Title FROM Articles";
                SqlCommand comm = new SqlCommand(strSQL, con);
                sdr = comm.ExecuteReader(CommandBehavior.CloseConnection);

                if (sdr != null)
                {
                    while (sdr.Read())
                    {
                        artList.Add(new Util.Article { Title = sdr["Title"].ToString(), ArticleID = sdr["ArticleID"].ToString() });
                    }
                }
            }

            return true;
        }

        public bool SendArticle(Util.Article art)
        {
            int count;

            using (SqlConnection con = new SqlConnection())
            {
                con.ConnectionString = ConfigurationManager.ConnectionStrings["FProj"].ConnectionString;

                String strSQL = "INSERT INTO Articles (UserName, Title, ArticleBody, Date) VALUES (@UserName, @Title, @ArticleBody, @Date)";
                SqlCommand comm = new SqlCommand(strSQL, con);

                SqlParameter param = new SqlParameter();
                param.ParameterName = "@UserName";
                param.Value = art.UserName;
                param.SqlDbType = SqlDbType.NVarChar;
                comm.Parameters.Add(param);

                param = new SqlParameter();
                param.ParameterName = "@Title";
                param.Value = art.Title;
                param.SqlDbType = SqlDbType.Text;
                comm.Parameters.Add(param);

                param = new SqlParameter();
                param.ParameterName = "@Date";
                param.Value = art.Date;
                param.SqlDbType = SqlDbType.DateTime;
                comm.Parameters.Add(param);

                param = new SqlParameter();
                param.ParameterName = "@ArticleBody";
                param.Value = art.ArticleBody;
                param.SqlDbType = SqlDbType.Text;
                comm.Parameters.Add(param);

                con.Open();

                count = comm.ExecuteNonQuery();
            }
            if (count != 0)
                return true;
            else
                return false;
        }

        public bool EditArticle(string id, Util.Article news)
        {
            int count;

            using (SqlConnection con = new SqlConnection())
            {
                con.ConnectionString = ConfigurationManager.ConnectionStrings["FProj"].ConnectionString;

                String strSQL = "UPDATE Articles SET Title = @Title, ArticleBody = @ArticleBody WHERE ArticleID = @ArticleID";
                SqlCommand comm = new SqlCommand(strSQL, con);

                SqlParameter param = new SqlParameter();
                param.ParameterName = "@ArticleID";
                param.Value = news.ArticleID;
                param.SqlDbType = SqlDbType.NVarChar;
                comm.Parameters.Add(param);

                param = new SqlParameter();
                param.ParameterName = "@Title";
                param.Value = news.Title;
                param.SqlDbType = SqlDbType.NVarChar;
                comm.Parameters.Add(param);

                param = new SqlParameter();
                param.ParameterName = "@ArticleBody";
                param.Value = news.ArticleBody;
                param.SqlDbType = SqlDbType.Text;
                comm.Parameters.Add(param);

                con.Open();

                count = comm.ExecuteNonQuery();
            }
            if (count != 0)
                return true;
            else
                return false;
        }

        public bool DeleteArticle(string id)
        {
            int count;

            using (SqlConnection con = new SqlConnection())
            {
                con.ConnectionString = ConfigurationManager.ConnectionStrings["FProj"].ConnectionString;

                String strSQL = "DELETE FROM Articles WHERE ArticleID = @ArticleID";
                SqlCommand comm = new SqlCommand(strSQL, con);

                SqlParameter param = new SqlParameter();
                param.ParameterName = "@ArticleID";
                param.Value = id;
                param.SqlDbType = SqlDbType.NVarChar;
                comm.Parameters.Add(param);

                con.Open();

                count = comm.ExecuteNonQuery();
            }
            if (count != 0)
                return true;
            else
                return false;
        }
    }
}